
package com.onlineclass.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.demo.util.JDBCUtils;
import com.onlineclass.bean.Course;
import com.onlineclass.bean.Order;

public class OrderDAO {
	QueryRunner qr = new QueryRunner();

	/***
	 * 创建订单
	 * 
	 * @param courseIds
	 * @param actualPrice
	 * @param userId
	 * @param orderNo
	 * @return
	 */
	public long save(String[] courseIds, String actualPrice, Integer userId, String orderNo) {
		long nums = 0;
		Connection conn = null;
		try {
			// 首先，订单表插入一条记录
			// 其次，订单明细表插入n条记录
			// 然后，从购物车删除已下单的课程
			// 应该在一个事务下完成
			conn = JDBCUtils.getConnection();
			conn.setAutoCommit(false);// 开启事务功能，禁止sql自动提交
			nums = qr.update(JDBCUtils.getConnection(),
					"insert into t_order(orderNo,userId,actualPrice,created) values(?,?,?,now())", orderNo, userId,
					actualPrice);
			StringBuffer sql = new StringBuffer("insert into t_orderdetail(courseId,orderNo) values");
			StringBuffer ids = new StringBuffer();
			for (int i = 0; i < courseIds.length; i++) {
				ids.append(courseIds[i]);
				sql.append(" (" + courseIds[i] + "," + orderNo + ")");
				if (i < courseIds.length - 1) {
					sql.append(",");
					ids.append(",");
				}
			}
			nums = qr.update(JDBCUtils.getConnection(), sql.toString());
			qr.update(JDBCUtils.getConnection(),
					"delete from t_cart where courseId in(" + ids + ") and" + " userId = ?", userId);
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nums;
	}

	/**
	 * 购买的课程
	 * @param userId
	 * @return
	 */
	public List<Order> list(Integer userId) {
		List<Order> orders = null;
		try {
			orders = qr.query(JDBCUtils.getConnection(), 
					"select * from t_order where userId=? order by created desc", new BeanListHandler<>(Order.class), userId);
			for (Order order : orders) {
				String orderNo = order.getOrderNo();
				//遍历每条订单，查询所含课程
				List<Course> courses = qr.query(JDBCUtils.getConnection(), 
						"select c.* from t_orderdetail od JOIN t_course c ON od.courseId=c.id where od.orderNo=?",
						new BeanListHandler<>(Course.class),
						orderNo);
				order.setCourses(courses);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return orders;
	}
	
	/**
	 * 回传修改某笔订单的支付流水号
	 * @param orderNo
	 * @param paySn
	 */
	public void pay(String orderNo, String paySn) {	
		try {		
		qr.update(JDBCUtils.getConnection(),
					"update t_order set paySn=?,payTime=now() where orderNo=?", orderNo, paySn);		
		} catch (Exception e) {
				e.printStackTrace();
		}
	}
}
